# -*- coding:utf-8 -*-#
# -------------------------------------------------------------------------
# ProjectName:       zhcx-service-test
# FileName:          common_methods.py
# Author:            xun
# Date:              2024/7/16 9:13
# Description:
# --------------------------------------------------------------------------
'''
    写入读库、请求接口
'''
from requests.sessions import Session
from openpyxl import Workbook #后续可能要将数据展示给业务,作为备选
from testcases.Settlement_order_monitoring.set_log import SetLogger
from datetime import date,timedelta,datetime
from pymongo import MongoClient
import pyodbc
import json

class DuMongoDB:

    def __init__(self):
        client = MongoClient('mongodb://116.205.165.248:8635,121.37.236.237',8635,username='rwuser',password='lgb1ndv9aUmLKsNMD3J8!')
        db = client['irobotbox_dataservice_financial']
        self.collection = db['amazon_financialevents_group_1']

    def get_Group_plus(self,startTime=None,endTime=None):
        # if not startTime and not endTime:
        #     now = datetime.now()
        #     startTime = datetime(now.year, now.month, now.day) - timedelta(days=4)
        #     endTime = datetime(now.year, now.month, now.day) - timedelta(days=2)
        now = datetime.now()
        if now.day >= 5:
            startTime = datetime(now.year, now.month, 1)
            endTime = datetime(now.year, now.month, now.day) - timedelta(days=1)
        else:
            last_month = datetime(now.year, now.month, 1) - timedelta(days=1)
            startTime = last_month.replace(day=1)
            endTime = last_month
        get_all_group=self.collection.find({'UpdatedAt':{'$gte': startTime,'$lte': endTime}},{'GroupId':1,'OrderSourceID':1,'_id':0}) #数据排查可通过skip()和limit()来限制
        get_all_group_mount = self.collection.find({'UpdatedAt': {'$gte': startTime, '$lte': endTime}},{'Item': 1, '_id': 0})
        get_all_merchantId = self.collection.find({'UpdatedAt': {'$gte': startTime, '$lte': endTime}},{'GroupId':1,'MerchantId':1,'_id':0})
        dict1,dict2 = {},{}
        for i in get_all_group_mount:
            i = dict(i)
            tuple1 = []
            tuple1.append(i['Item']['FinancialEventGroupId'])
            tuple1.append(i['Item']['OriginalTotal']['CurrencyAmount'])
            dict1[i['Item']['FinancialEventGroupId']] = tuple1
        for k in get_all_merchantId:
            dict2[k['GroupId']] = k['MerchantId']
        return get_all_group,dict1,startTime,endTime,dict2

class DuSql:
    '''
    目前先做sqlserver,后续加入mysql
    '''

    def __init__(self):
        self.server = 'erpdb-readonly.zhcxkj.com'  # sql server
        self.host = 'erpdb-readonly.zhcxkj.com'  # mysql
        self.user = 'irobotboxuser'
        self.password = 'HskwPanda047RAHRAxqktlref'
        self.database = 'irobotbox'
        self.port = 1433
        # self.table=PrettyTable()
        self.charset = 'ISO-8859-1'
        self.connect = pyodbc.connect((f'DRIVER={{SQL Server}};SERVER={self.server},{self.port};DATABASE={self.database};UID={self.user};PWD={self.password}'))
        self.cousor = self.connect.cursor()

    def get_all_merchantId(self):
        sql = "SELECT distinct DevIDSellerId  FROM RB_OrderSource_EbayAmazonAPI WHERE CustomerID = 1 and " \
              "DevIDSellerId <> \'\' and IsActive =1 and OrderSourceID in (select DISTINCT ID  from RB_OrderSource ros WHERE CustomerID = 1 and OrderSourceType = 1)"
        self.cousor.execute(sql)
        return self.cousor.fetchall()

    def get_rb_amazonListFinancialEventGroups(self,FinancialEventGroupId):
        '''单独获取'''
        sql = "SELECT OrderSourceID FROM RB_Amazon_ListFinancialEventGroups ros WHERE CustomerID = 1 AND FinancialEventGroupId=\'{0}\'".format(FinancialEventGroupId)
        self.cousor.execute(sql)
        return self.cousor.fetchone()

    def get_all_rb_amazonListFinancialEventGroups(self,FinancialEventGroupId):
        sql = "SELECT FinancialEventGroupId FROM RB_Amazon_ListFinancialEventGroups(NOLOCK) ros WHERE CustomerID = 1 AND FinancialEventGroupId in (" + FinancialEventGroupId + ")"
        self.cousor.execute(sql)
        return self.cousor.fetchall()

    def get_rb_amazonListFinancialEvents(self,FinancesReportID):
        sql='select FinancesReportID,SUM(CurrencyAmount)  from RB_Amazon_ListFinancialEvents ralfe WHERE CustomerID = 1 and FinancesReportID in ('+FinancesReportID+') group by FinancesReportID'
        self.cousor.execute(sql)
        return self.cousor.fetchall()

    def get_rb_amazonListFinancialEvents_puls(self,FinancesReportID):
        sql = "select tt.b,SUM(tt.a)  from (select FinancesReportID b,SUM(CurrencyAmount)+SUM(PointsGrantedCurrencyAmount)/COUNT(*)+SUM(PointsReturnedCurrencyAmount)/COUNT(*) a  " \
                "from RB_Amazon_ListFinancialEvents ralfe WHERE CustomerID = 1 and FinancesReportID in ("+FinancesReportID+")  GROUP BY FinancesReportID,AmazonOrderId) tt group by tt.b"
        self.cousor.execute(sql)
        return self.cousor.fetchall()

    def check_orderSource_status(self,MerchantId):
        sql = "select IsActive from RB_OrderSource_EbayAmazonAPI roseaa WHERE CustomerID = 1 and DevIDSellerId = \'{0}\'".format(MerchantId)
        self.cousor.execute(sql)
        return self.cousor.fetchall()

class sent_request:

    def __init__(self):
        self.s=Session()
        self.headers = {
            "Content_Type": "application/json",
            "Authorization": "Basic YXBpdXNlcjppMXI1bzhCb3Rib3g="
        }

    def get_Group(self,merchantId,startTime=None,endTime=None):
        url=r'http://dataservice.zhcxkj.com/amazon/FinancialGroup'
        if not startTime and not endTime:
            today = date.today()
            startTime = str(today - timedelta(days=2))
            endTime = str(today - timedelta(days=1))
        data1 = {
                "customerId":1,
                "merchantId":merchantId,
                "startTime":startTime,
                "endTime":endTime
                 }
        request_result = self.s.post(url=url,json=data1,headers=self.headers).json()
        return request_result['data']


    def get_events(self,groupId):
        url=r'http://dataservice.zhcxkj.com/amazon/FinancialEvents'
        data2 = {
                "customerId": 1,
                "groupId": groupId
                }
        request_result = self.s.post(url=url, json=data2, headers=self.headers).json()
        return request_result['data']

    def send_dingding_message(self,message,webhook_url=r"https://oapi.dingtalk.com/robot/send?access_token=81aabd44e5c1b493fde67801a60dcd8a6735f378819cc1de0e878d436f626789"):
        headers = {
            'Content-Type': 'application/json',
            'Charset': 'UTF-8'
        }
        data = {
            "msgtype": "text",
            "text": {
                "content": message
            },
            "at":{
                "atMobiles":["18681557567","18578798740"]
            }
        }
        response = self.s.post(url=webhook_url, headers=headers, data=json.dumps(data))
        if response.status_code == 200:
            print("Message sent successfully")
        else:
            print(f"Failed to send message, error code: {response.status_code}")

class create_log:

    def __init__(self):
        self.wb=Workbook()
        self.log=SetLogger()

    def create_excel(self,content:list,title:list=None):
        sheet=self.wb.active
        sheet.title='sheet1'
        # sheet.append(title)
        if content:
            sheet.append(content)
        self.wb.save('./savelog/%s.xlsx' % date.today())

    def create_txt(self,content):
        self.log.logger.info(f"日志打印》》》》{content}")

class more_tool:
    '''多线程(源数据接口性能太差,目前只能加在查库上)、加个定时脚本'''
    def data_conversion(self,data):
        '''将[('XXX', Decimal('-11')),('XXX', Decimal('-11'))] 转换成 [('XXX', -11)),('XXX', -11')]'''
        data1 = []
        for i in data:
            tuple1 = []
            tuple1.append(i[0])
            if i[1] is not None: #先排除event表sum(CurrencyAmount)为零的数据
                tuple1.append(float(i[1]))
            data1.append(tuple(tuple1))
        return data1


    def check_all_data(self,all_orderSource_status):
        '''判断卖家ID'''
        num2 = 0
        for i in all_orderSource_status:
            if i[0]:
                num2 = 1
        return num2

if __name__ == '__main__':
    ds= DuSql()
    s=sent_request()
    mt=more_tool()
    dmD=DuMongoDB()
    # print(ds.get_all_merchantId())
    # print(s.get_Group('A100EX20D7AJEJ'))
    c=create_log()
    # c.create_excel([1,12345,123])
    list2 =['测试一下']
    c.create_txt('接口数据:{0}'.format(list2))
    list1 =['aYeQRezuq97bdTNoQMsDm-IcQepOwfPyC6cpxwTUTmk',
            'BMy0NjjSELphXS3Gf63iC_FFPQWZT7ac0nhLNC-m6HA',
            '0GtBcW-U8k-AZjfiBzRSV2_bMs2aGISSraZqN4ft2BU',
            '0GtBcW-U8k-AZjfiBzRSV2_bMs2aGISSraZqN4ft2BU']

    str1 = '\''+'\',\''.join(list1)+'\''
    # print(ds.get_all_rb_amazonListFinancialEventGroups(str1))
    # data1 = ds.get_rb_amazonListFinancialEvents(str1)
    # data1=mt.data_conversion(ds.get_rb_amazonListFinancialEvents(str1))
    # data2=mt.data_conversion(ds.get_rb_amazonListFinancialEvents_puls(str1))
    # print(set(data1)-set(data2))
    num1 = 0
    dict1={}
    # for i in dmD.get_Group_plus()[0]:
    #     dict1[i['GroupId']]=i['OrderSourceID']
    # print(len(dict1.keys()),dict1)
    result1 = ds.check_orderSource_status('A3KY3N71EFFAK5')
    print(mt.check_all_data(result1))
